package com.guhappy.model.user;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.apache.struts2.ServletActionContext;
import org.hibernate.HibernateException;
import org.hibernate.ScrollableResults;
import org.hibernate.SessionFactory;

import com.guhappy.action.model.ChartPageBean;
import com.guhappy.action.model.ListPrice;
import com.guhappy.action.model.PieChartPageBean;
import com.guhappy.util.HibernateListener;

import edu.emory.mathcs.backport.java.util.Collections;

public class StockPriceHistrHome {
	private static final Logger log = Logger.getLogger(StockPriceHistrHome.class);
	private static final SessionFactory sessionFactory = getSessionFactory();

	protected static SessionFactory getSessionFactory() {
		SessionFactory sessionFactory = 
	         (SessionFactory) ServletActionContext.getServletContext()
                    .getAttribute(HibernateListener.KEY_NAME);
		return sessionFactory;
	}
	
	public static ChartPageBean getPriceList(String stockId){
		ChartPageBean aBean = new ChartPageBean();
		List<ListPrice> aList = new ArrayList<ListPrice>();
		List bList = new ArrayList();
		StringBuffer buf = new StringBuffer();
		StringBuffer buf2 = new StringBuffer();
		
		buf.append("select TO_CHAR(STOCK_PRICE_DATE, 'YYYY-MM-DD'), STOCK_HISTORY_CLOSE, VOLUME/1000000 from STOCK_PRICE_HISTORY where F_STOCK_ID = '");
		buf.append(stockId);
		buf.append("' and STOCK_PRICE_DATE between add_months(sysdate,-12) and sysdate order by STOCK_PRICE_DATE asc");
		try {
			sessionFactory.getCurrentSession().beginTransaction();
			ScrollableResults rset = sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).scroll();
			while (rset.next()){
				aList.add(new ListPrice((String) rset.get(0),((BigDecimal)rset.get(1)).floatValue(), ((BigDecimal)rset.get(2)).floatValue()));
				bList.add((BigDecimal)rset.get(1));
			}
			int max = Math.round(((BigDecimal) Collections.max(bList)).floatValue());
			int min = Math.round(((BigDecimal) Collections.min(bList)).floatValue());
			aBean.setListPrice(aList);
			aBean.setMax(max);
			aBean.setMin(min);
			aBean.setStep(Math.round((max-min)/5));
			sessionFactory.getCurrentSession().getTransaction().commit();
		} catch (HibernateException e) {
			e.printStackTrace();
		}
		return aBean;
	}
	
	public static PieChartPageBean getPieInfo(String stockId, String indexId){
		PieChartPageBean aBean = new PieChartPageBean();
		
		StringBuffer buf = new StringBuffer();
		StringBuffer buf2 = new StringBuffer();
		StringBuffer buf3 = new StringBuffer();
		StringBuffer buf4 = new StringBuffer();
		StringBuffer buf5 = new StringBuffer();
		StringBuffer buf6 = new StringBuffer();
		StringBuffer buf7 = new StringBuffer();
		StringBuffer buf8 = new StringBuffer();
		
		/*select count(*) from (select a.stockweekvalue-b.indexweekvalue beat from (select adate, STOCK_HISTORY_CLOSE-weekvalue stockweekvalue from (select TO_CHAR(STOCK_PRICE_DATE, 'YYYY-MM-DD') adate, STOCK_HISTORY_CLOSE,lead(STOCK_HISTORY_CLOSE,5,0) over(order by STOCK_PRICE_DATE desc) weekvalue from STOCK_PRICE_HISTORY where F_STOCK_ID = '4028b8814890c441014890c4db15006c' 
				and STOCK_PRICE_DATE between add_months(sysdate,-12) and sysdate)) a  left join 
				(select adate, STOCK_HISTORY_CLOSE-weekvalue indexweekvalue from (select TO_CHAR(STOCK_PRICE_DATE, 'YYYY-MM-DD') adate, STOCK_HISTORY_CLOSE,lead(STOCK_HISTORY_CLOSE,5,0) over(order by STOCK_PRICE_DATE desc) weekvalue from STOCK_PRICE_HISTORY where F_STOCK_ID = '4028b8814890c441014890c46492000c' 
				and STOCK_PRICE_DATE between add_months(sysdate,-12) and sysdate) ) b on a.adate = b.adate) c where c.beat>0*/
		
		buf.append("select count(*) from (select a.stockweekvalue-b.indexweekvalue beat from (select adate, STOCK_HISTORY_CLOSE-weekvalue stockweekvalue from (select TO_CHAR(STOCK_PRICE_DATE, 'YYYY-MM-DD') adate, STOCK_HISTORY_CLOSE,lead(STOCK_HISTORY_CLOSE,5,0) over(order by STOCK_PRICE_DATE desc) weekvalue from STOCK_PRICE_HISTORY where F_STOCK_ID = '");
		buf.append(stockId);
		buf.append("')) a  left join (select adate, STOCK_HISTORY_CLOSE-weekvalue indexweekvalue from (select TO_CHAR(STOCK_PRICE_DATE, 'YYYY-MM-DD') adate, STOCK_HISTORY_CLOSE,lead(STOCK_HISTORY_CLOSE,5,0) over(order by STOCK_PRICE_DATE desc) weekvalue from STOCK_PRICE_HISTORY where F_STOCK_ID = '");
		buf.append(indexId);
		buf.append("') ) b on a.adate = b.adate) c where c.beat>0");
		
		buf2.append("select count(*) from (select a.stockweekvalue-b.indexweekvalue beat from (select adate, STOCK_HISTORY_CLOSE-weekvalue stockweekvalue from (select TO_CHAR(STOCK_PRICE_DATE, 'YYYY-MM-DD') adate, STOCK_HISTORY_CLOSE,lead(STOCK_HISTORY_CLOSE,5,0) over(order by STOCK_PRICE_DATE desc) weekvalue from STOCK_PRICE_HISTORY where F_STOCK_ID = '");
		buf2.append(stockId);
		buf2.append("')) a  left join (select adate, STOCK_HISTORY_CLOSE-weekvalue indexweekvalue from (select TO_CHAR(STOCK_PRICE_DATE, 'YYYY-MM-DD') adate, STOCK_HISTORY_CLOSE,lead(STOCK_HISTORY_CLOSE,5,0) over(order by STOCK_PRICE_DATE desc) weekvalue from STOCK_PRICE_HISTORY where F_STOCK_ID = '");
		buf2.append(indexId);
		buf2.append("') ) b on a.adate = b.adate) c where c.beat<0");
		
		buf3.append("select count(*) from (select a.stockweekvalue-b.indexweekvalue beat from (select adate, STOCK_HISTORY_CLOSE-weekvalue stockweekvalue from (select TO_CHAR(STOCK_PRICE_DATE, 'YYYY-MM-DD') adate, STOCK_HISTORY_CLOSE,lead(STOCK_HISTORY_CLOSE,20,0) over(order by STOCK_PRICE_DATE desc) weekvalue from STOCK_PRICE_HISTORY where F_STOCK_ID = '");
		buf3.append(stockId);
		buf3.append("')) a  left join (select adate, STOCK_HISTORY_CLOSE-weekvalue indexweekvalue from (select TO_CHAR(STOCK_PRICE_DATE, 'YYYY-MM-DD') adate, STOCK_HISTORY_CLOSE,lead(STOCK_HISTORY_CLOSE,20,0) over(order by STOCK_PRICE_DATE desc) weekvalue from STOCK_PRICE_HISTORY where F_STOCK_ID = '");
		buf3.append(indexId);
		buf3.append("') ) b on a.adate = b.adate) c where c.beat>0");
		
		buf4.append("select count(*) from (select a.stockweekvalue-b.indexweekvalue beat from (select adate, STOCK_HISTORY_CLOSE-weekvalue stockweekvalue from (select TO_CHAR(STOCK_PRICE_DATE, 'YYYY-MM-DD') adate, STOCK_HISTORY_CLOSE,lead(STOCK_HISTORY_CLOSE,20,0) over(order by STOCK_PRICE_DATE desc) weekvalue from STOCK_PRICE_HISTORY where F_STOCK_ID = '");
		buf4.append(stockId);
		buf4.append("')) a  left join (select adate, STOCK_HISTORY_CLOSE-weekvalue indexweekvalue from (select TO_CHAR(STOCK_PRICE_DATE, 'YYYY-MM-DD') adate, STOCK_HISTORY_CLOSE,lead(STOCK_HISTORY_CLOSE,20,0) over(order by STOCK_PRICE_DATE desc) weekvalue from STOCK_PRICE_HISTORY where F_STOCK_ID = '");
		buf4.append(indexId);
		buf4.append("') ) b on a.adate = b.adate) c where c.beat<0");
		
		buf5.append("select count(*) from (select a.stockweekvalue-b.indexweekvalue beat from (select adate, STOCK_HISTORY_CLOSE-weekvalue stockweekvalue from (select TO_CHAR(STOCK_PRICE_DATE, 'YYYY-MM-DD') adate, STOCK_HISTORY_CLOSE,lead(STOCK_HISTORY_CLOSE,63,0) over(order by STOCK_PRICE_DATE desc) weekvalue from STOCK_PRICE_HISTORY where F_STOCK_ID = '");
		buf5.append(stockId);
		buf5.append("')) a  left join (select adate, STOCK_HISTORY_CLOSE-weekvalue indexweekvalue from (select TO_CHAR(STOCK_PRICE_DATE, 'YYYY-MM-DD') adate, STOCK_HISTORY_CLOSE,lead(STOCK_HISTORY_CLOSE,63,0) over(order by STOCK_PRICE_DATE desc) weekvalue from STOCK_PRICE_HISTORY where F_STOCK_ID = '");
		buf5.append(indexId);
		buf5.append("') ) b on a.adate = b.adate) c where c.beat>0");
		
		buf6.append("select count(*) from (select a.stockweekvalue-b.indexweekvalue beat from (select adate, STOCK_HISTORY_CLOSE-weekvalue stockweekvalue from (select TO_CHAR(STOCK_PRICE_DATE, 'YYYY-MM-DD') adate, STOCK_HISTORY_CLOSE,lead(STOCK_HISTORY_CLOSE,63,0) over(order by STOCK_PRICE_DATE desc) weekvalue from STOCK_PRICE_HISTORY where F_STOCK_ID = '");
		buf6.append(stockId);
		buf6.append("')) a  left join (select adate, STOCK_HISTORY_CLOSE-weekvalue indexweekvalue from (select TO_CHAR(STOCK_PRICE_DATE, 'YYYY-MM-DD') adate, STOCK_HISTORY_CLOSE,lead(STOCK_HISTORY_CLOSE,63,0) over(order by STOCK_PRICE_DATE desc) weekvalue from STOCK_PRICE_HISTORY where F_STOCK_ID = '");
		buf6.append(indexId);
		buf6.append("') ) b on a.adate = b.adate) c where c.beat<0");
		
		buf7.append("select count(*) from (select a.stockweekvalue-b.indexweekvalue beat from (select adate, STOCK_HISTORY_CLOSE-weekvalue stockweekvalue from (select TO_CHAR(STOCK_PRICE_DATE, 'YYYY-MM-DD') adate, STOCK_HISTORY_CLOSE,lead(STOCK_HISTORY_CLOSE,253,0) over(order by STOCK_PRICE_DATE desc) weekvalue from STOCK_PRICE_HISTORY where F_STOCK_ID = '");
		buf7.append(stockId);
		buf7.append("')) a  left join (select adate, STOCK_HISTORY_CLOSE-weekvalue indexweekvalue from (select TO_CHAR(STOCK_PRICE_DATE, 'YYYY-MM-DD') adate, STOCK_HISTORY_CLOSE,lead(STOCK_HISTORY_CLOSE,253,0) over(order by STOCK_PRICE_DATE desc) weekvalue from STOCK_PRICE_HISTORY where F_STOCK_ID = '");
		buf7.append(indexId);
		buf7.append("') ) b on a.adate = b.adate) c where c.beat>0");
		
		buf8.append("select count(*) from (select a.stockweekvalue-b.indexweekvalue beat from (select adate, STOCK_HISTORY_CLOSE-weekvalue stockweekvalue from (select TO_CHAR(STOCK_PRICE_DATE, 'YYYY-MM-DD') adate, STOCK_HISTORY_CLOSE,lead(STOCK_HISTORY_CLOSE,253,0) over(order by STOCK_PRICE_DATE desc) weekvalue from STOCK_PRICE_HISTORY where F_STOCK_ID = '");
		buf8.append(stockId);
		buf8.append("')) a  left join (select adate, STOCK_HISTORY_CLOSE-weekvalue indexweekvalue from (select TO_CHAR(STOCK_PRICE_DATE, 'YYYY-MM-DD') adate, STOCK_HISTORY_CLOSE,lead(STOCK_HISTORY_CLOSE,253,0) over(order by STOCK_PRICE_DATE desc) weekvalue from STOCK_PRICE_HISTORY where F_STOCK_ID = '");
		buf8.append(indexId);
		buf8.append("') ) b on a.adate = b.adate) c where c.beat<0");
		
		
		try {
			sessionFactory.getCurrentSession().beginTransaction();
			ScrollableResults rset = sessionFactory.getCurrentSession().createSQLQuery(buf.toString()).scroll();
			while (rset.next()){
					aBean.setBeatIndexWeekly(((BigDecimal)rset.get(0)).intValue());
			}
			rset = sessionFactory.getCurrentSession().createSQLQuery(buf2.toString()).scroll();
			while (rset.next()){
				aBean.setLostIndexWeekly(((BigDecimal)rset.get(0)).intValue());
			}
			
			rset = sessionFactory.getCurrentSession().createSQLQuery(buf3.toString()).scroll();
			while (rset.next()){
				aBean.setBeatIndexMonthly(((BigDecimal)rset.get(0)).intValue());
			}
			rset = sessionFactory.getCurrentSession().createSQLQuery(buf4.toString()).scroll();
			while (rset.next()){
				aBean.setLostIndexMonthly(((BigDecimal)rset.get(0)).intValue());
			}
			rset = sessionFactory.getCurrentSession().createSQLQuery(buf5.toString()).scroll();
			while (rset.next()){
				aBean.setBeatIndexQuaterly(((BigDecimal)rset.get(0)).intValue());
			}
			rset = sessionFactory.getCurrentSession().createSQLQuery(buf6.toString()).scroll();
			while (rset.next()){
				aBean.setLostIndexQuaterly(((BigDecimal)rset.get(0)).intValue());
			}
			rset = sessionFactory.getCurrentSession().createSQLQuery(buf7.toString()).scroll();
			while (rset.next()){
				aBean.setBeatIndexYearly(((BigDecimal)rset.get(0)).intValue());
			}
			rset = sessionFactory.getCurrentSession().createSQLQuery(buf8.toString()).scroll();
			while (rset.next()){
				aBean.setLostIndexYearly(((BigDecimal)rset.get(0)).intValue());
			}

			sessionFactory.getCurrentSession().getTransaction().commit();
		} catch (HibernateException e) {
			e.printStackTrace();
		}
		return aBean;
	}
	
	
	
	
}
